package intranet.models;
import java.sql.*;

import javax.security.auth.Subject;
public class MyuploadModel {
	Connection DBConnection=null;
	public MyuploadModel(){
		DatabaseModel DBModel=new DatabaseModel();
		DBConnection=DBModel.GetConnection();
	}
		/* SELECT UPLOADED FILE BY ID */
	public ResultSet SelectUploadedFile(int file_id){
		ResultSet result=null;
		String Conditional_String=(file_id!=0)?" WHERE F.FILE_ID="+file_id:"";
		try{
			Statement ConStatement=DBConnection.createStatement();
			String Sql="SELECT F.FILE_ID,F.FILE_NAME,U.USER_NAME,F.FILE_PATH,F.FILE_TITLE,C.CATEGORY_ID,C.CATEGORY_NAME,DBMS_LOB.SUBSTR(FILE_DESC,DBMS_LOB.GETLENGTH(FILE_DESC),1) AS FILE_DESC,F.FREQUENCY,F.DATE_UPLOAD,F.USER_UPLOAD FROM (UPLOADED_FILES F INNER JOIN FILE_CATEGORIES C ON C.CATEGORY_ID=F.CATEGORY_ID) INNER JOIN USER_ACCOUNTS U ON F.USER_UPLOAD=U.USER_ID "+Conditional_String+" ORDER BY F.DATE_UPLOAD DESC";
			result=ConStatement.executeQuery(Sql);
		}catch(Exception e){
			e.getLocalizedMessage();
		}
		return result;
	}	
	
		/* SELECT FILE KEYWORDS BY FILE ID */
	public String SelectFileKeywords(int file_id) throws SQLException{
		String result="";
		ResultSet rst=null;
		try{
			Statement ConStatement=DBConnection.createStatement();
			String Sql="SELECT KEYWORD FROM FILE_KEYWORDS WHERE FILE_ID="+ file_id;
			rst=ConStatement.executeQuery(Sql);
		}catch(Exception e){
			e.getLocalizedMessage();
		}
		int i=0;
		while(rst.next()){
			result+=rst.getString("KEYWORD")+";";
			i++;
		}
		result=(i==0)?"":result.toString().substring(0, result.length()-1); 
		return result;
	}	
	
		/* SELECT MY UPLOADED FILE */
	public ResultSet SelectMyupload(int user_id){
		ResultSet result=null;
		try{
			Statement ConStatement=DBConnection.createStatement();
			String Sql="SELECT F.FILE_ID,F.FILE_NAME,F.FILE_PATH,F.FILE_TITLE,DBMS_LOB.SUBSTR(F.FILE_DESC,DBMS_LOB.GETLENGTH(F.FILE_DESC),1) AS FILE_DESC,C.CATEGORY_ID,C.CATEGORY_NAME,F.FREQUENCY,TO_CHAR(F.DATE_UPLOAD,'DD/MM/YYYY') AS DATE_UPLOAD FROM UPLOADED_FILES F INNER JOIN FILE_CATEGORIES C ON C.CATEGORY_ID=F.CATEGORY_ID WHERE F.USER_UPLOAD="+ user_id+" ORDER BY F.FILE_ID DESC";
			result=ConStatement.executeQuery(Sql);
		}catch(Exception e){
			e.getLocalizedMessage();
		}
		return result;
	}
	
	/* Verify File Owner */
	public boolean isOwner(int userid,int fileid){
		boolean result=false;
		int i=0;
		ResultSet rst=null;
		try{
			Statement ConStatement=DBConnection.createStatement();
			String Sql="SELECT FILE_ID FROM UPLOADED_FILES WHERE USER_UPLOAD="+userid+" AND FILE_ID="+fileid+"";
			rst=ConStatement.executeQuery(Sql);
			while(rst.next()){
				i++;
			}
		}catch(Exception e){
			e.getLocalizedMessage();
		}
		result=(i==0)?false:true;
		return result;
	}

	/* SELECT MY UPLOADED FILE BY CATEGORY */
	public ResultSet SelectMyuploadByCategory(int user_id,int cat_id){
		ResultSet result=null;
		try{
			Statement ConStatement=DBConnection.createStatement();
			String Sql="";
			if(cat_id==0){
				Sql="SELECT F.FILE_ID,F.FILE_NAME,F.FILE_PATH,F.FILE_TITLE,DBMS_LOB.SUBSTR(F.FILE_DESC,DBMS_LOB.GETLENGTH(F.FILE_DESC),1) AS FILE_DESC,C.CATEGORY_ID,C.CATEGORY_NAME,F.FREQUENCY,TO_CHAR(F.DATE_UPLOAD,'DD/MM/YYYY') AS DATE_UPLOAD FROM UPLOADED_FILES F INNER JOIN FILE_CATEGORIES C ON C.CATEGORY_ID=F.CATEGORY_ID WHERE F.USER_UPLOAD="+ user_id+" ORDER BY F.FILE_ID DESC";				
			}else{
				Sql="SELECT F.FILE_ID,F.FILE_NAME,F.FILE_PATH,F.FILE_TITLE,DBMS_LOB.SUBSTR(F.FILE_DESC,DBMS_LOB.GETLENGTH(F.FILE_DESC),1) AS FILE_DESC,C.CATEGORY_ID,C.CATEGORY_NAME,F.FREQUENCY,TO_CHAR(F.DATE_UPLOAD,'DD/MM/YYYY') AS DATE_UPLOAD FROM UPLOADED_FILES F INNER JOIN FILE_CATEGORIES C ON C.CATEGORY_ID=F.CATEGORY_ID WHERE F.USER_UPLOAD="+ user_id+" AND F.CATEGORY_ID="+cat_id+" ORDER BY F.FILE_ID DESC";
			}
			result=ConStatement.executeQuery(Sql);
		}catch(Exception e){
			e.getLocalizedMessage();
		}
		return result;
	}

		/* INSERT MY UPLOAD */
	public void InsertMyupload(String title,int cat_id,String file_desc,double file_size,String file_name,String file_path,String keywords,int user_id){
		ResultSet result=null;
		
		try{
			Statement ConStatement=DBConnection.createStatement();
			String Sql="INSERT INTO UPLOADED_FILES (FILE_ID,FILE_TITLE,CATEGORY_ID,FILE_DESC,FILE_SIZE,FREQUENCY,FILE_NAME,FILE_PATH,DATE_UPLOAD,USER_UPLOAD)";
			Sql+="VALUES(UPLOADSEQ.NEXTVAL,'"+title+"',"+cat_id+",'"+file_desc+"',"+file_size+",0,'"+file_name+"','"+file_path+"',SYSDATE,"+user_id+")";
			result=ConStatement.executeQuery(Sql);
			if(keywords!=""){
				ResultSet keyResult=null;
				long fileId= this.getMaxId();
				Statement keyStatement=DBConnection.createStatement();
				String[] arrkeys=keywords.split(";");
				for(int i=0;i<arrkeys.length;i++){
					String sqlKey="INSERT INTO FILE_KEYWORDS (FILE_ID,KEYWORD) VALUES("+fileId+",'"+arrkeys[i]+"')";
					keyResult=keyStatement.executeQuery(sqlKey);
				}
			}
			
		}catch(Exception e){
			e.getLocalizedMessage();
		}
	}
		/* GET MAX ID */
	public long getMaxId() throws SQLException{
		ResultSet result=null;
		long id=1;
		try{
			Statement ConStatement=DBConnection.createStatement();
			String Sql="SELECT UPLOADSEQ.NEXTVAL AS FILE_ID FROM DUAL";
			result=ConStatement.executeQuery(Sql);
		}catch(Exception e){
			e.getLocalizedMessage();
		}
		while(result.next()){
			id=result.getLong("FILE_ID");
		}
		return id-1;
		
	}
		/* UPDATE MY UPLOAD */
	public void UpdateMyupload(int id,String title,int cat_id,String file_desc,String keywords){
		ResultSet result=null;
		try{
			Statement ConStatement=DBConnection.createStatement();
			String Sql="UPDATE UPLOADED_FILES SET FILE_TITLE='"+title+"',CATEGORY_ID="+cat_id+",FILE_DESC='"+file_desc+"' WHERE FILE_ID="+id;
			result=ConStatement.executeQuery(Sql);
			if(keywords!=""){
				ResultSet keyResult=null;
				ResultSet keyResultDel=null;
				Statement keyStatement=DBConnection.createStatement();
				String sqlkeydel="DELETE FROM FILE_KEYWORDS WHERE FILE_ID="+id;
				keyResultDel=keyStatement.executeQuery(sqlkeydel);
				String[] arrkeys=keywords.split(";");
				for(int i=0;i<arrkeys.length;i++){
					String sqlKey="INSERT INTO FILE_KEYWORDS (FILE_ID,KEYWORD) VALUES("+id+",'"+arrkeys[i]+"')";
					keyResult=keyStatement.executeQuery(sqlKey);
				}
			}
		}catch(Exception e){
			e.getLocalizedMessage();
		}
	}
	
	/* Update Myuploads */
	public void DeleteMyupload(int id){
		ResultSet result=null;
		try{
			Statement ConStatement=DBConnection.createStatement();
			String Sql="DELETE FROM UPLOADED_FILES WHERE FILE_ID="+id;
			result=ConStatement.executeQuery(Sql);
		}catch(Exception e){
			e.getLocalizedMessage();
		}
	}
	
	/* SELECT USER SHARED */
public ResultSet SelectUsersShared(int file_id){
	ResultSet result=null;
	try{
		Statement ConStatement=DBConnection.createStatement();
		String Sql="SELECT U.USER_ID,U.USER_NAME,S.FILE_ID FROM FILE_ACCESSIBLE_USERS S INNER JOIN USER_ACCOUNTS U ON U.USER_ID=S.USER_ID WHERE S.FILE_ID="+file_id;
		result=ConStatement.executeQuery(Sql);
	}catch(Exception e){
		e.getLocalizedMessage();
	}
	return result;
}
/* Add User to Share */
public void AddUser(int file_id,int user_id ){
	ResultSet result=null;
	try{
		Statement ConStatement=DBConnection.createStatement();
		String Sql="INSERT INTO FILE_ACCESSIBLE_USERS (FILE_ID,USER_ID) VALUES("+file_id+","+user_id+")";
		result=ConStatement.executeQuery(Sql);
	}catch(Exception e){
		e.getLocalizedMessage();
	}
}
/* Delete User to Share */
public void DeleteUser(int file_id,int user_id ){
	ResultSet result=null;
	try{
		Statement ConStatement=DBConnection.createStatement();
		String Sql="DELETE FROM FILE_ACCESSIBLE_USERS WHERE FILE_ID="+file_id+" AND USER_ID="+user_id;
		result=ConStatement.executeQuery(Sql);
	}catch(Exception e){
		e.getLocalizedMessage();
	}
}

/* Add GROUP to Share */
public void AddGroup(int file_id,int group_id ){
ResultSet result=null;
try{
	Statement ConStatement=DBConnection.createStatement();
	String Sql="INSERT INTO FILE_ACCESSIBLE_GROUPS (FILE_ID,GROUP_ID) VALUES("+file_id+","+group_id+")";
	result=ConStatement.executeQuery(Sql);
}catch(Exception e){
	e.getLocalizedMessage();
}
}

/* Delete Group to Share */
public void DeleteGroup(int file_id,int group_id ){
	ResultSet result=null;
	try{
		Statement ConStatement=DBConnection.createStatement();
		String Sql="DELETE FROM FILE_ACCESSIBLE_GROUPS WHERE FILE_ID="+file_id+" AND GROUP_ID="+group_id;
		result=ConStatement.executeQuery(Sql);
	}catch(Exception e){
		e.getLocalizedMessage();
	}
}

/* SELECT GROUP SHARED */
public ResultSet SelectGroupsShared(int file_id){
ResultSet result=null;
try{
	Statement ConStatement=DBConnection.createStatement();
	String Sql="SELECT G.GROUP_ID,G.GROUP_NAME,D.FILE_ID FROM FILE_ACCESSIBLE_GROUPS D INNER JOIN USER_DEFINED_GROUPS G ON G.GROUP_ID=D.GROUP_ID WHERE D.FILE_ID="+file_id;
	result=ConStatement.executeQuery(Sql);
}catch(Exception e){
	e.getLocalizedMessage();
}
return result;
}

/* SELECT GROUP MEMBERS */
public ResultSet SelectGroupMembers(int group_id){
ResultSet result=null;
try{
	Statement ConStatement=DBConnection.createStatement();
	String Sql="SELECT * FROM GROUP_MEMBERS WHERE GROUP_ID="+group_id;
	result=ConStatement.executeQuery(Sql);
}catch(Exception e){
	e.getLocalizedMessage();
}
return result;
}

/* GET USER_ID BY USER_NAME FROM USER_ACCOUNTS */
public int getUserIdByUserName(String user_name){
	int result=0;
	ResultSet rst=null;
	try{
		Statement ConStatement=DBConnection.createStatement();
		String Sql="SELECT USER_ID,USER_NAME FROM USER_ACCOUNTS WHERE USER_NAME='"+user_name+"'";
		rst=ConStatement.executeQuery(Sql);
		while(rst.next()){
			result=rst.getInt("USER_ID");
		}
	}catch(Exception e){
		e.getLocalizedMessage();
	}
	return result;
}

/* VERIFY IF EXISTING User */
public boolean isExistingUser(String user_name,String file_id){
	boolean result=false;
	int count=0;
	// Get User ID
	int userid=this.getUserIdByUserName(user_name);
	ResultSet rst=null;
	try{
		Statement ConStatement=DBConnection.createStatement();
		String Sql="SELECT * FROM FILE_ACCESSIBLE_USERS WHERE USER_ID="+userid+" AND FILE_ID="+file_id;
		rst=ConStatement.executeQuery(Sql);
		while(rst.next()){
			count++;
		}
		result=(count>0)?true:false;
	}catch(Exception e){
		e.getLocalizedMessage();
	}
	return result;
}

/* VERIFY IF EXISTING GROUP */
public boolean isExistingGroup(String group_id,String file_id){
	boolean result=false;
	int count=0;
	ResultSet rst=null;
	try{
		Statement ConStatement=DBConnection.createStatement();
		String Sql="SELECT * FROM FILE_ACCESSIBLE_GROUPS WHERE GROUP_ID="+group_id+" AND FILE_ID="+file_id+"";
		rst=ConStatement.executeQuery(Sql);
		while(rst.next()){
			count++;
		}
		result=(count>0)?true:false;
	}catch(Exception e){
		e.getLocalizedMessage();
	}
	return result;
}

/* INSERT ACCESSIBLE USER */
public void insertSharedUser(int file_id,int user_id){
	ResultSet result=null;
	try{
		Statement ConStatement=DBConnection.createStatement();
		String sql="INSERT INTO FILE_ACCESSIBLE_USERS (FILE_ID,USER_ID) VALUES("+file_id+","+user_id+")";
		result=ConStatement.executeQuery(sql);
	}catch(Exception e){
		e.getLocalizedMessage();
	}
}
/* SELECT MY GROUPS */
public ResultSet SelectMyGroups(int user_id){
ResultSet result=null;
try{
	Statement ConStatement=DBConnection.createStatement();
	String Sql="SELECT M.GROUP_ID,G.GROUP_NAME FROM GROUP_MEMBERS M INNER JOIN USER_DEFINED_GROUPS G ON G.GROUP_ID=M.GROUP_ID WHERE M.USER_ID="+user_id;
	result=ConStatement.executeQuery(Sql);
}catch(Exception e){
	e.getLocalizedMessage();
}
return result;
}
/* SELECT GROUP NAME BY GROUP ID */
public String SelectGroupNameById(String group_id){
	String result="";
	ResultSet rst=null;
	try{
		Statement ConStatement=DBConnection.createStatement();
		String Sql="SELECT GROUP_NAME FROM USER_DEFINED_GROUPS WHERE GROUP_ID="+group_id;
		rst=ConStatement.executeQuery(Sql);
		while(rst.next()){
			result=rst.getString("GROUP_NAME");
		}
	}catch(Exception e){
		e.getLocalizedMessage();
	}
	return result;
	
}

/* IS FILE SHARED TO USER */
public boolean isSharedToUser(int file_id){
	boolean result=false;
	ResultSet rst=null;
	String Sql="";
	int i=0;
	try{
			Statement ConStatement=DBConnection.createStatement();
			Sql="SELECT USER_ID FROM FILE_ACCESSIBLE_USERS WHERE FILE_ID="+file_id;
			rst=ConStatement.executeQuery(Sql);
			while(rst.next()){
				i++;
			}	
	   }catch(Exception e){e.getLocalizedMessage();}
	result=(i>0)?true:false;
	return result;
}

/* IS FILE SHARED TO GROUP */
public boolean isSharedToGroup(int file_id){
	boolean result=false;
	ResultSet rst=null;
	String Sql="";
	int i=0;
	try{
			Statement ConStatement=DBConnection.createStatement();
			Sql="SELECT GROUP_ID FROM FILE_ACCESSIBLE_GROUPS WHERE FILE_ID="+file_id;
			rst=ConStatement.executeQuery(Sql);
			while(rst.next()){
				i++;
			}	
	}catch(Exception e){
		e.getLocalizedMessage();
	}
	
	result=(i>0)?true:false;
	return result;
}

/* SET FILE SHARE STATUS - IS PUBLIC OR NOT */
public void setShareStatus(int file_id){
	ResultSet rst=null;
	String Sql="";
	try{
		Statement ConStatement=DBConnection.createStatement();
		if(isSharedToUser(file_id)==false && isSharedToGroup(file_id)==false){
			Sql="UPDATE UPLOADED_FILES SET IS_PUBLIC=1 WHERE FILE_ID="+file_id;
		}else{
			Sql="UPDATE UPLOADED_FILES SET IS_PUBLIC=0 WHERE FILE_ID="+file_id;
		}
		rst=ConStatement.executeQuery(Sql);
	}catch(Exception e){
		e.getLocalizedMessage();
	}
}

/* SELECT ALL USERS AND PASS TO USERS STRING */
public String SelectUsers(){
	ResultSet result=null;
	//String strx="da.born"+'"';
	String strname="";
	int i=0;
	String Sql="";
	try{
		Statement ConStatement=DBConnection.createStatement();
			Sql="SELECT USER_ID,USER_NAME FROM USER_ACCOUNTS";
			result=ConStatement.executeQuery(Sql);
			while(result.next())
				if(i==0){
					strname+=result.getString("USER_NAME")+'"'+","+'"';
				}else{
					strname+='"'+result.getString("USER_NAME")+'"'+",";
			    }
			
	   }catch(Exception e){e.getLocalizedMessage();}
	return strname;
	}
}
